Parallelizing i/o processing of index insertions during insertion of data into a database

ABSTRACT

Database elements are inserted into a database object by processing each of a plurality of operations in a sequential order within a first processing round to insert the database elements into the database objects, where processing for at least one operation in the order becomes suspended due to a resource request, and where at least one successive operation is initiated in response to suspension of one or more prior operations to enable prefetching of information for processing the operations. Each suspended operation is re-processed with the prefetched information in one or more additional processing rounds until processing of the operations is completed.

BACKGROUND

1. Technical Field

The present invention relates to insertion of data into a database, suchas SQL (structured query language) insertions in relational databasemanagement systems including multiple indexes.

2. Discussion of the Related Art

Data storage and maintenance operations within a database areparticularly important for a variety of applications in which the needfor relatively easy and quick access to large amounts of information isrequired. Structured Query Language (SQL) is a commonly used databasecomputer language used for managing data in database systems such asrelational database management systems (RDMS). Database systems such asRDMS utilize a database model in which data is stored in one or moretables with one or more indexes to access data in the table(s).Depending upon the amount of data in the database, multiple indexes canbe useful to provide rapid random look ups and also efficient access andordering of records. The indexes of a database are typically formattedas an index tree with nodes, including an initial root node,intermediate nodes and terminal nodes (also referred to as leaf pages).

Data records can be added to the table(s) of the database by performingan SQL INSERT function. When an SQL INSERT statement is executed toinsert a data record into a database table, corresponding insertoperations need to be performed on all the indexes defined on the sametable. Typically, such insert operations are performed in a sequentialmanner. Thus, each insert operation of a record into an index has to befinished before the insert operation into the next index can beinitiated. In scenarios in which the number of indexes defined on atable is large and the index pages needed for performing insertoperations are not within a buffer pool, the processor performing theoperations must wait for index page input/output (I/O) operations tofinish for all indexes before the record insertion step can becompleted. Therefore, the SQL INSERT operation for inserting a recordinto the database can encounter high response time due to the sequentialwait times associated with processing each index page I/O operation.

One method for avoiding high wait times associated with the index pageI/O operations involves performing parallel insertions on the indexes,which utilizes a separate processing task per index. While this methodcan effectively reduce the I/O wait times for accessing index pages,there is the potentially undesirable consequence of utilizing a large orexcessive amount of central processing unit (CPU) capacity due to themultiple processing tasks being performed in parallel for the insertionsand coordinating their completion.

BRIEF SUMMARY

Accordingly, embodiments of the present invention include a method, acomputer program product and a system for processing operations toinsert database elements into a database object, in which the processingoperations comprise processing each of a plurality of operations in asequential order within a first processing round to insert the databaseelements into the database objects, where processing for at least oneoperation in the order becomes suspended due to a resource request, andwhere at least one successive operation is initiated in response tosuspension of one or more prior operations to enable prefetching ofinformation for processing the operations. The processing operationsfurther comprise re-processing with the prefetched information eachsuspended operation in one or more additional processing rounds untilcompletion of processing for each of the operations.

The above and still further features and advantages of embodiments ofthe present invention will become apparent upon consideration of thefollowing detailed description thereof, particularly when taken inconjunction with the accompanying drawings wherein like referencenumerals in the various figures are utilized to designate likecomponents.

BRIEF DESCRIPTION OF THE SEVERAL VIEWS OF THE DRAWINGS

FIGS. 1A and 1B provide a flowchart depicting an example method ofperforming an SQL INSERT function with parallelizing of I/O index pageoperations for insertion of a record into a database according to anembodiment of the present invention.

FIG. 2 is a block diagram illustrating an example processor and databasefor performing an SQL INSERT function with I/O parallel processing ofindex page insertions in accordance with the present invention.

DETAILED DESCRIPTION

In accordance with embodiments of the present invention, a method,system and computer program product provides efficient parallelprocessing or parallelizing of index page input/output (I/O) operationsduring insertion of a database record within a database. The presentinvention facilitates the insertion of information relating to a datarecord into two or more indexes defined within a database table inparallel so as to minimize I/O wait time while performing theparallelizing as a single processing task. Providing a single processtask to perform the insertion of such information within the tableindexes results in minimal or no increase in central processing unit(CPU) capacity. Instead, the example embodiments of the presentinvention can facilitate a decrease in CPU capacity during the insertionof database records within a database because of reduced suspendoperations that might otherwise occur during such insert operations.

In an example embodiment in accordance with the present invention,performance of a Structured Query Language (SQL) INSERT commandoperation to insert one or more records within a table of a database isenhanced by facilitating a procedure in which the I/O operations toprovide each index of the table with appropriate information relating toeach inserted record can be parallelized so as to reduce I/O wait time.

The database includes one or more tables to store the records, whereeach row of a table includes a record and the columns include fields ofdata for a particular record. A plurality of indexes are defined withthe one or more tables to provide quick and easy look up and access to arecord stored in a table or insertion/deletion of a record within thetable by identifying the record location in the index tree with a keyvalue and a RID (row identification) value unique to the particularindex. These values are located at the terminal nodes, also referred toas leaf pages, of the index tree. For example, a particular recordstored within a table of a database can be easily accessed and retrievedutilizing one or more indexes of the database, in which an index treecan be navigated to a specific leaf page utilizing the RID and keyvalues to navigate through the index tree to the specific location ofthe record to be accessed within the table.

When an SQL INSERT statement is executed to insert a record into atable, corresponding insert operations need to be performed on all theindexes defined on the same table. This is typically performed in asequential manner, where a processor, such as a database search engine,populates a buffer pool with the nodes for each index, includingintermediate nodes or non-leaf pages and also the leaf pages for eachindex. When there are a large number of index pages for a table, theindex pages needed to complete insertion of the record in the table maynot be in the buffer pool. The processor must wait for an index page I/Ooperation to be complete for insertion of information before proceedingto insertion of information within the next index page. The I/O waittimes for retrieval of each subsequent index page can add up and resultin higher response times to achieve an SQL INSERT operation,particularly when there are a large number (for example, hundreds ofthousands or millions) of records to be inserted within a table.

In the example embodiment of the present invention, a determination ismade whether to sequentially wait for the leaf page of each index to bewithin the buffer pool for a particular record to be inserted within thedatabase. If the determination is to not wait, a prefetch request isissued by the database search engine for retrieval of the index leafpage into the buffer pool while also proceeding forward to retrieve thenext index leaf page corresponding with the inserted record.

An example method is depicted in the flowchart of FIGS. 1A and 1B, wherethe method facilitates parallelizing, when desired, of I/O index pageoperations during an SQL INSERT statement in which multiple records areinserted within a database table including multiple indexes. In thisexample embodiment, there are N records to be inserted within a databasetable having I indexes, where N and I can be any selected number. Themethod can be particularly useful in minimizing I/O wait times inscenarios in which the number N of records for insertion and/or thenumber of indexes defined for a table is very large. For example, thenumber of records N can be on the order from 10² to 10⁶ or even greater.The number of indexes I is smaller, typically 10 or less, but can alsobe as much as 10-20 or even greater.

In a first step of the SQL INSERT operation (step 10), the record N andindex I are set at 1 to designate the first record to be insert and thefirst index of the table to be processed in the operation. Record N isinserted as a row within the table (step 20), and a get page request isperformed (step 30) to retrieve the leaf page of index I from a bufferpool that is designated to store index pages. A determination is made(step 40) regarding whether the leaf page for index I and correspondingwith record N is in the buffer pool. The buffer pool may already store aportion of certain indexes including index I, such as non-leaf pages. Itis not likely, at least in the first pass of the SQL INSERT operation inwhich the first record is being inserted, that the leaf page for index Iis within the buffer pool. However, in the event this leaf page iswithin the buffer pool, the key and RID values corresponding with recordN are inserted into index I at this leaf page (step 60), and the processthen skips to step 100.

When a determination is made that the leaf page needed for index I isnot within the buffer pool, a decision is made (step 50) regardingwhether to perform parallelizing of index I/O operations. An analysis(as described in further detail below) can be performed to determinewhether it would be beneficial to perform parallelizing, such that theparallelizing operation (steps 80 and 90 of FIGS. 1A and 1B) isselectively performed. In the event the determination is to not performparallelizing of index I/O operations, the database search engine waitsfor the index Ito further populate until the requested leaf page isaccessible within the buffer pool (step 70), and the key and RID valuescorresponding with record N are then inserted at this leaf page of indexI (step 60). The process then proceeds to step 100.

In the event a determination is made to perform parallelizing of indexI/O operations, a request is performed to prefetch within the bufferpool the leaf page corresponding with record N for index I (step 80).Referring to FIG. 1B, the database engine saves key and RID informationcorresponding with record N and for index I in a memory structure (e.g.,a temporary storage location assigned within a memory structure of thedatabase) (step 90). The database engine also stores informationregarding the leaf page number for this record N and for index I, sothat it is easy to revisit the leaf page for insertion of the key andRID values (instead of having to re-traverse the index tree) when theleaf page becomes available for insertion.

A determination is made whether the last index of the table has beenprocessed (step 100). In the event unprocessed indexes still remain, thenext index is processed for the same record N utilizing the sameprocessing steps 30-100. In the flowchart steps shown in FIG. 1B, thevalue of I is increased to the next index (step 110) and the process isrepeated starting with a get page request for the next index Icorresponding with record N (step 30).

When the last index has been processed for record N, a determination ismade (step 120) regarding whether a prefetch for any table index wasrequired and thus resulted in key, RID and leaf page information havingbeen stored in the memory structure. In the event no prefetch requestswere performed for any table index in relation to record N, the processproceeds to step 140.

In the event a prefetch request was performed for any table index inrelation to record N, the database search engine continues another roundof performing get leaf page requests in a sequential order for theremaining indexes that have not yet had insertions (step 130). The savedkey, RID and leaf page information provide the insert position for eachindex so that the location within each index tree can be navigated toinsert the key and RID values when the leaf page for each index has beenpopulated within the buffer pool. At this point in the process, thecurrent record N has been inserted into the database table and all tableindexes have been updated with corresponding key and RID values tofacilitate navigational access to this inserted record via the indexes.

A determination is made (step 140) whether the last record has beeninserted. In the event the last record has been inserted into thedatabase table, the SQL INSERT process is complete. In the event one ormore further records are to be inserted, the process of inserting thenext record N and performing index insertions associated with the nextrecord N is repeated. In particular, as shown in FIG. 1B (step 150), thevalue of N is increased by 1 to designate the next record, while thevalue of I is set back to 1 such that insertion of key and RID valuesfor the next record N is initiated with the first index, and the processis then repeated beginning with step 20.

In the method described above, the first round of get index pagerequests for a particular record N can be made conditional (steps 30, 50and 80) so as to initiate I/O parallelizing of the index insertions. Thesecond round of get index page requests (step 130) for the remainingindexes in which a prefetch request was required in the first round isunconditional, since the database search engine waits until the leafpage for a particular index is populated within the buffer pool forindex insertion prior to proceeding to the next index requiringinsertion. However, the method can be optionally modified to provide theI/O parallelizing of index insertions as an iterative process, in whichthe get page request for indexes remains conditional in the second roundand any selected number of subsequent rounds. In other words, a repeatof steps 40, 50 and 80 as shown in FIG. 1A can occur any selected numberof times for index pages not within the buffer pool, where indexes withkey and RID information stored within the memory structure arerepeatedly revisited as necessary until their index pages are populatedwithin the buffer pool.

A simple, non-limiting example is now described in which records are tobe added to a table including three indexes I1, I2 and I3 utilizing themethod described above and depicted in the flowchart of FIGS. 1A and 1B.After a row of data corresponding with record N1 is inserted into thistable, all indexes I1, I2 and I3 need to be inserted with thecorresponding index key value and RID value. Without using the parallelI/O operation for insertion into the indexes, if an index page neededfor insert is not in the buffer pool, the database engine would need towait until that index page is brought into the buffer pool and thencomplete the insert before an insertion would be performed into the nextindex. Thus, all of the I/O waits for the indexes I1, I2 and I3 would beperformed sequentially, increasing the overall elapsed time forprocessing the SQL INSERT operation for record N1 and potentiallysubsequent records.

When the parallel insert operation is activated (i.e., step 50 in FIG.1A), parallelizing of the index I/O operations can occur such that asubsequent index (for example, index I2) can be processed for insertionwhile a prefetch request is being simultaneously processed for aprevious index (for example, index I1). In the above example, at leastsome of the non-leaf pages for one or more of the indexes I1, I2 and I3may already be in the buffer pool. In the get page request for the firstindex IL the key and RID information is used to traverse the index treefor index I1 to locate the leaf page for insertion. The get page requestfor such leaf page number is conditional. Thus, in the event this leafpage is not in the buffer pool, instead of waiting for this page to bebrought into the buffer pool synchronously, the database search engineissues a prefetch request to bring in this leaf page into buffer poolasynchronously (step 80). The database engine then saves the key and RIDinformation along with the leaf page number providing the insertposition for index I1 into a memory structure (e.g., a temporary storagelocation of the database).

The database engine then proceeds to the next index I2 and performs thesame series of process steps. Thus, in the event the leaf page forinsertion in index I2 is not in the buffer pool, the same prefetchrequest will be made for this leaf page and the database engine savesthe key, RID and leaf page number providing the index insert positioninformation into the memory structure before proceeding to the nextindex I3. In the event the leaf page required for index I3 is already inbuffer pool, the insertion of the key and RID values for index I3 can befinished with no I/O wait time. After all indexes have been processedfor a first time to either to perform an insertion or to prefetch theleaf page needed for insert, the database engine will examine the memorystructure and then perform the insert into indexes I1 and I2 which havebeen skipped in the first round of the process (step 130).

Typically, the I/O time needed for bringing in an index page into thebuffer pool is significantly longer than the CPU time needed forinserting into the leaf page. Therefore, when index I1 is revisited inthe process by the database search engine, there is a chance that theprefetch request for the leaf page associated with this index is stillin progress. The database search engine performs the get page requestfor this leaf page of index I1 as an unconditional request, waitinguntil this leaf page is populated within the buffer pool to facilitatecompletion of insertion into index I1 of the key and RID values prior tomoving to the next index I2 that needs to be revisited. For each of theindexes I1 and I2, since the key, RID and leaf page number informationhave been saved in the memory structure, the database search engine cansave time and avoid the cost of regenerating the key and re-traversingthe index tree in order to perform the insert.

In the previous example including a table with three indexes I1, I2 andI3, the I/O wait time for index insertion operations does not occurseparately (i.e., where the processor waits until the leaf page isavailable for insertion into index I1 before proceeding to index I2,etc.) but is instead parallelized in the first round. This may result inan I/O wait time reduction by as much as a factor of 2, since theprefetching for leaf pages of indexes I1 and I2 are occurringsimultaneously as the insertion occurs for index I3. In another examplein which there are five indexes defined for a table where each tableinsertion encounters four index I/O waits (i.e., only one out of thefive indexes has it's corresponding leaf page in the buffer pool whilethe rest of the indexes require an I/O wait to bring in the leaf pagesinto the buffer pool for insertion), the I/O wait time might be fourtimes greater than when practicing the example method according to theinvention (i.e., four I/O waits for indexes I1-I4 are reduced to one I/Owait since all four I/O prefetch requests occur simultaneously). Thedegree of the I/O wait time reduction can be significantly enhanced asthe number of indexes defined on a table increases and also when thenumber of records to be inserted within the table increases.

As noted above in the example embodiment (at step 50), the parallelizingcan be selectively activated or deactivated (i.e., turned on or off)during a process depending upon a particular scenario. In particular,real time statistical information can be used to control the usage ofI/O parallelism for index insertion. For example, if there is only asmall number of indexes encountering a buffer miss on the get pagerequest (i.e., no leaf page in the buffer pool for the index I), runningwith I/O parallelism for index insertion operations may not reduce theelapsed time but instead increase CPU time. Real time statistics can beused to track the history of get page requests for index insertion onthe table, with I/O parallelism being activated when a certain number ofindexes (e.g., the majority of indexes) encounter a set number of buffermisses (e.g., two or more buffer misses) during index get page requests.The I/O parallelism for index insertion operations can be dynamicallyenabled or disabled during the process to obtain optimal performanceduring the SQL INSERT process.

In an example process in which an insert workload includes a largenumber records, such as 1 million or more records, and a plurality ofindexes, there might be a large number (e.g., a majority) of the leafpages for the indexes that are not in the buffer pool at the start ofthe workload (e.g., during the insertion of the first few thousand ormore of the records into the table). In this scenario, activation of I/Oparallelism during index insertion would be beneficial at least for aselected number of records. After a certain period of time during theprocess (e.g., after insertion of 100,000 or more records), a suitablenumber of the index leaf pages may already be populated within thebuffer pool, such that the I/O wait period may be significantly reduceddue to index page population presently within the buffer pool inrelation to the initial time period in which records were first insertedwithin the table and the buffer pool was less populated. Based upon realtime statistical information, the I/O parallelism operation can beselectively deactivated during the SQL INSERT process in the event thestatistical information provides a determination that the CPU capacityrequired to conduct the I/O parallelism process becomes disadvantageousin relation to the reduced I/O index insertion wait time that is beingpresently observed.

An example of real time statistical information that can be used toselectively activate or deactivate the I/O parallelism for indexinsertion operations is provided as follows. At any given time duringthe process, the current total number N of records inserted can becompared with a current number Y of record insertions that encountered aselected number (e.g., two or more) of index I/O waits (i.e., the numberof indexes that did not have their leaf pages within the buffer pool andthus required a prefetch request). The ratio of Y/X can be determined.In the event the ratio of Y/X is greater than a predetermined thresholdvalue, the I/O parallelism remains activated (or can be selectivelyactivated if the I/O parallelism was inactive). Alternatively, in theevent the value of Y/X is not greater than the predetermined thresholdvalue, the I/O parallelism can be selectively deactivated (or remaininactive).

It is noted that the invention is not limited to the above examples forusing real time statistical information for activation or deactivationof I/O parallelizing for index insertion operations. Any suitablemechanism for determining whether and to what extent I/O waits areoccurring can be utilized to facilitate an automated activation ordeactivation of the I/O parallelizing processing steps. Thus, the recordinsertion process can be “self-monitoring”to activate or turn on the I/Oparallelizing of index insertion operations when it is determined thatthere are too many I/O waits and to deactivate such operations when itis determined that there are few I/O waits. The determination of when toactivate/de-activate the I/O parallelizing of index insertion operationscan be adjusted or tunable to obtain a suitable trade-off betweenelapsed time and CPU constraints based upon a particular scenario.

Thus, the selective activation and deactivation of the I/O parallelismfor index insertion enhances the efficiency of the record insertionprocess by reducing I/O wait times while optimizing CPU performance.

In the examples described above, I/O parallelism for index insertionoperations occurs for processing of a single record as each record/rowis sequentially inserted within a database table. In other words, I/Oparallelizing for index insertion operations occurs for a first recordbeing inserted within a database table prior to I/O parallelizingoccurring for the next sequential record to be inserted within thedatabase table. However, it is noted that the invention is not limitedto such sequential processing of records with I/O parallelizingoperations. The present invention is also applicable for parallelizingof index insertion operations across different row insertions in adatabase table. In other words, a plurality of records can besimultaneously processed or processed in parallel for row insertionwithin a table, where I/O parallelizing of index insertion operationscan occur (as necessary) for each record being processed in parallel.This provides a further benefit in reducing wait times for a largenumber of records even when a small number of indexes (e.g., a singleindex or two or three indexes) exist for a database table.

The methods described above and depicted in the flowchart of FIGS. 1Aand 1B can be implemented utilizing any suitable system includinghardware and software implemented in a single module or unit or,alternatively, implemented as two or more separate units. An exampleembodiment of a system for implementing the method is schematicallydepicted in FIG. 2. In particular, system 200 includes a record database202 and a server 204 that is operatively coupled to the database 202.The record database 202 and server 204 can be implemented together asingle hardware unit or, alternatively, implemented in any two or morehardware units coupled together. The server 204 includes a CPU, adatabase search engine and one or more memory structures which providestorage locations for the buffer pool and storage of index informationassociated with prefetch operations. The server facilitates operationsincluding, without limitation, retrieval, insertion and deletion ofrecords within the database. The server 204 can receive records forstorage on the database 202 from any one or more suitable sources and/orin any suitable manner including, without limitation, via direct dataentry of record information to the server, via cloud computing toreceive record information, via network computing in which the server isoperatively coupled to one or more other servers to receive recordinformation, via any suitable computer readable storage medium thatincludes record information (e.g., magnetic, optical, floppy, DVD, CD,other non-transitory medium, etc.), and via any suitable type of carrierwave or signal for downloading from a source via a communication medium(e.g., bulletin board, network, LAN, WAN, Intranet, Internet, etc.).

The server and database can be any suitable computer systems implementedby any type of hardware and/or other processing circuitry. Inparticular, the server and database may be implemented by any quantityof conventional or other computer systems or devices (e.g., computerterminals, personal computers (e.g., IBM-compatible, Apple MacIntosh,tablet, laptop, etc.), etc.), cellular telephones, personal dataassistants (e.g., Palm Pre, Droid, iPhone, etc.), etc., and may includeany commercially available operating system (e.g., AIX, Android, Linux,OSX, Sun Solaris, Unix, Windows, etc.) and any commercially available orcustom software (e.g., browser software, communications software, wordprocessing software, etc.). These systems may include types of displaysand input devices (e.g., keyboard, mouse, voice recognition, etc.) toenter and/or view information. In addition, record information may bereceived by the server and retained by the database in any suitablemanner, such as the types described above.

In operation, the server 204 can insert records into the database 202utilizing the method described above and depicted in the flowchart ofFIGS. 1A and 1B. The server 204 receives record information and performsthe operation of inserting the records as rows in a table for storage bydatabase 202. The selective I/O parallelizing of index insertionoperations is performed as necessary during the record insertionprocess, based upon statistical information obtained by the server CPUduring the process relating to the availability of index pages in thebuffer pool and number of prefetch requests required for processedrecords.

The methods and systems described above are not limited to optimizingrecord insertion processes by reducing I/O wait times. Other possibleapplications utilizing example methods and systems of the presentinvention include, without limitation, processes in which waits occurdue to data locks for security or other reasons (e.g., p-locks in adatasharing environment), and any other processes in which waits arerequired for any resource in which the resource can be requestedasynchronously to perform an insertion (e.g., tree lotches, treelatches, controlling disk resources, etc.).

As will be appreciated by one skilled in the art, aspects of the presentinvention may be embodied as a system, method or computer programproduct. Accordingly, aspects of the present invention may take the formof an entirely hardware embodiment, an entirely software embodiment(including firmware, resident software, micro-code, etc.) or anembodiment combining software and hardware aspects that may allgenerally be referred to herein as a “circuit,” “module” or “system.”Furthermore, aspects of the present invention may take the form of acomputer program product embodied in one or more computer readablemedium(s) having computer readable program code embodied thereon.

Any combination of one or more computer readable medium(s) may beutilized. The computer readable medium may be a computer readable signalmedium or a computer readable storage medium. A computer readablestorage medium may be, for example, but not limited to, an electronic,magnetic, optical, electromagnetic, infrared, or semiconductor system,apparatus, or device, or any suitable combination of the foregoing. Morespecific examples (a non-exhaustive list) of the computer readablestorage medium would include the following: an electrical connectionhaving one or more wires, a portable computer diskette, a hard disk, arandom access memory (RAM), a read-only memory (ROM), an erasableprogrammable read-only memory (EPROM or Flash memory), an optical fiber,a portable compact disc read-only memory (CD-ROM), an optical storagedevice, a magnetic storage device, or any suitable combination of theforegoing. In the context of this document, a computer readable storagemedium may be any tangible medium that can contain, or store a programfor use by or in connection with an instruction execution system,apparatus, or device.

A computer readable signal medium may include a propagated data signalwith computer readable program code embodied therein, for example, inbaseband or as part of a carrier wave. Such a propagated signal may takeany of a variety of forms, including, but not limited to,electro-magnetic, optical, or any suitable combination thereof. Acomputer readable signal medium may be any computer readable medium thatis not a computer readable storage medium and that can communicate,propagate, or transport a program for use by or in connection with aninstruction execution system, apparatus, or device.

Program code embodied on a computer readable medium may be transmittedusing any appropriate medium, including but not limited to wireless,wireline, optical fiber cable, RF, etc. or any suitable combination ofthe foregoing.

Computer program code for carrying out operations for aspects of thepresent invention may be written in any combination of one or moreprogramming languages, including an object oriented programming languagesuch as Java, Smalltalk, C++ or the like and conventional proceduralprogramming languages, such as the “C” programming language or similarprogramming languages. The program code may execute entirely on theuser's computer, partly on the user's computer, as a stand-alonesoftware package, partly on the user's computer and partly on a remotecomputer or entirely on the remote computer or server. In the latterscenario, the remote computer may be connected to the user's computerthrough any type of network, including a local area network (LAN) or awide area network (WAN), or the connection may be made to an externalcomputer (for example, through the Internet using an Internet ServiceProvider).

Aspects of the present invention are described with reference toflowchart illustrations and/or block diagrams of methods, apparatus(systems) and computer program products according to embodiments of theinvention. It will be understood that each block of the flowchartillustrations and/or block diagrams, and combinations of blocks in theflowchart illustrations and/or block diagrams, can be implemented bycomputer program instructions. These computer program instructions maybe provided to a processor of a general purpose computer, specialpurpose computer, or other programmable data processing apparatus toproduce a machine, such that the instructions, which execute via theprocessor of the computer or other programmable data processingapparatus, create means for implementing the functions/acts specified inthe flowchart and/or block diagram block or blocks.

These computer program instructions may also be stored in a computerreadable medium that can direct a computer, other programmable dataprocessing apparatus, or other devices to function in a particularmanner, such that the instructions stored in the computer readablemedium produce an article of manufacture including instructions whichimplement the function/act specified in the flowchart and/or blockdiagram block or blocks.

The computer program instructions may also be loaded onto a computer,other programmable data processing apparatus, or other devices to causea series of operational steps to be performed on the computer, otherprogrammable apparatus or other devices to produce a computerimplemented process such that the instructions which execute on thecomputer or other programmable apparatus provide processes forimplementing the functions/acts specified in the flowchart and/or blockdiagram block or blocks.

The flowchart and block diagrams in the figures illustrate thearchitecture, functionality, and operation of possible implementationsof systems, methods and computer program products according to variousembodiments of the present invention. In this regard, each block in theflowchart or block diagrams may represent a module, segment, or portionof code, which comprises one or more executable instructions forimplementing the specified logical function(s). It should also be notedthat, in some alternative implementations, the functions noted in theblock may occur out of the order noted in the figures. For example, twoblocks shown in succession may, in fact, be executed substantiallyconcurrently, or the blocks may sometimes be executed in the reverseorder, depending upon the functionality involved. It will also be notedthat each block of the block diagrams and/or flowchart illustration, andcombinations of blocks in the block diagrams and/or flowchartillustration, can be implemented by special purpose hardware-basedsystems that perform the specified functions or acts, or combinations ofspecial purpose hardware and computer instructions.

The terminology used herein is for the purpose of describing particularembodiments only and is not intended to be limiting of the invention. Asused herein, the singular forms “a”, “an” and “the” are intended toinclude the plural forms as well, unless the context clearly indicatesotherwise. It will be further understood that the terms “comprises”and/or “comprising,” when used in this specification, specify thepresence of stated features, integers, steps, operations, elements,and/or components, but do not preclude the presence or addition of oneor more other features, integers, steps, operations, elements,components, and/or groups thereof.

The corresponding structures, materials, acts, and equivalents of allmeans or step plus function elements in the claims below are intended toinclude any structure, material, or act for performing the function incombination with other claimed elements as specifically claimed. Thedescription of the present invention has been presented for purposes ofillustration and description, but is not intended to be exhaustive orlimited to the invention in the form disclosed. Many modifications andvariations will be apparent to those of ordinary skill in the artwithout departing from the scope and spirit of the invention. Theembodiment was chosen and described in order to best explain theprinciples of the invention and the practical application, and to enableothers of ordinary skill in the art to understand the invention forvarious embodiments with various modifications as are suited to theparticular use contemplated.

1. A computer-implemented method of processing operations to insertdatabase elements into a database object comprising: processing each ofa plurality of operations in a sequential order within a firstprocessing round to insert the database elements into the databaseobject, wherein processing for at least one operation in the orderbecomes suspended due to a resource request, and wherein at least onesuccessive operation is initiated in response to suspension of one ormore prior operations to enable prefetching of information forprocessing the operations; and re-processing with the prefetchedinformation each suspended operation in one or more additionalprocessing rounds until completion of processing for each of theoperations.
 2. The method of claim 1, wherein the database objectcomprises a database table with a plurality of indexes providing accessto information in the database table, each database element is insertedwithin a corresponding row within the database table, each indexcomprises a plurality of leaf pages, each leaf page provides informationassociated with a location of at least one database element within thedatabase table, the resource request comprises a request for an indexleaf page during an operation within the sequential order, and theinsertion of database elements into the database object includesinsertion of information at index leaf pages corresponding with thedatabase elements.
 3. The method of claim 2, wherein the processing eachof a plurality of operations in a sequential order within a firstprocessing round further comprises: determining, in a sequential orderof the indexes, whether index leaf pages corresponding with eachdatabase element are available in a buffer pool; in response to anyunavailable index leaf page corresponding with a database element notbeing in the buffer pool, executing a prefetch request into the bufferpool for the unavailable index leaf page while simultaneouslydetermining whether a next index leaf page in the sequential ordercorresponding with the database element is available in the buffer pool;and inserting into the available index leaf pages information associatedwith corresponding database elements.
 4. The method of claim 3, whereinthe re-processing with the prefetched information further comprises:inserting information associated with remaining database elements havingunavailable index leaf pages from the first round into available indexleaf pages in response to the unavailable index leaf pages correspondingwith the remaining database elements becoming available within thebuffer pool.
 5. The method of claim 2, wherein the database elements areinserted in parallel within rows of the database table, such that theprocessing each of a plurality of operations in a sequential orderwithin a first processing round and the re-processing with theprefetched information each suspended operation in one or moreadditional processing rounds are performed in parallel for at least twodatabase elements.
 6. The method of claim 2, wherein the processing eachof a plurality of operations in a sequential order within a firstprocessing round and the re-processing with the prefetched informationeach suspended operation in one or more additional processing rounds areperformed in a sequential order for each index of the database tablecorresponding with at least one database element such that all indexleaf pages corresponding with a single database element are insertedwith information associated with the single database element prior toproceeding to processing indexes for another database element.
 7. Themethod of claim 2, further comprising: subsequent to insertion ofinformation for all index leaf pages corresponding with a set number ofdatabase elements, determining for remaining database elements whetherto continue prefetching and re-processing with prefetched informationfor suspended operations; and in response to a determination todiscontinue prefetching and re-processing with prefetched informationfor suspended operations, processing indexes in a sequential order foreach remaining database element such that each leaf page of an indexbeing processed is obtained from the buffer pool and inserted withinformation corresponding with a remaining database element prior toproceeding to a next index to be processed in the sequential order forthe remaining database element.
 8. The method of claim 2, whereinsuspension of an operation in the order due to a resource request occurswhen an index leaf page to be inserted with information correspondingwith a database element is absent from the buffer pool, and prefetchingof information with at least one successive operation being initiatedcomprises prefetching of the absent index leaf page into the buffer poolin parallel with processing the next index in the sequential order.
 9. Acomputer program product for processing operations to insert databaseelements into a database object comprising: a computer readable storagemedium having computer readable program code embodied therewith, thecomputer readable program code configured to: process each of aplurality of operations in a sequential order within a first processinground to insert the database elements into the database object, whereinprocessing for at least one operation in the order becomes suspended dueto a resource request, and wherein at least one successive operation isinitiated in response to suspension of one or more prior operations toenable prefetching of information for processing the operations; andre-process with the prefetched information each suspended operation inone or more additional processing rounds until completion of processingfor each of the operations.
 10. The computer program product of claim 9,wherein the database object comprises a database table with a pluralityof indexes providing access to information in the database table, eachdatabase element is inserted within a corresponding row within thedatabase table, each index comprises a plurality of leaf pages, eachleaf page provides information associated with a location of at leastone database element within the database table, the resource requestcomprises a request for an index leaf page during an operation withinthe sequential order, and the insertion of database elements into thedatabase object includes insertion of information at index leaf pagescorresponding with the database elements.
 11. The computer programproduct of claim 10, wherein the computer readable program code isfurther configured to process each of a plurality of operations in asequential order within a first processing round by: determining, in asequential order of the indexes, whether index leaf pages correspondingwith each database element are available in a buffer pool; in responseto any unavailable index leaf page corresponding with a database elementnot being in the buffer pool, executing a prefetch request into thebuffer pool for the unavailable index leaf page while simultaneouslydetermining whether a next index leaf page in the sequential ordercorresponding with the database element is available in the buffer pool;and inserting into the available index leaf pages information associatedwith corresponding database elements.
 12. The computer program productof claim 11, wherein the computer readable program code is furtherconfigured to re-process with the prefetched information each suspendedoperation in one or more additional processing rounds by: insertinginformation associated with remaining database elements havingunavailable index leaf pages from the first round into available indexleaf pages in response to the unavailable index leaf pages correspondingwith the remaining database elements becoming available within thebuffer pool.
 13. The computer program product of claim 10, wherein thecomputer readable storage medium is configured to insert the databaseelements in parallel within rows of the database table, such that theprocessing each of a plurality of operations in a sequential orderwithin a first processing round and the re-processing with theprefetched information each suspended operation in one or moreadditional processing rounds are performed in parallel for at least twodatabase elements.
 14. The computer program product of claim 10, whereinthe processing each of a plurality of operations in a sequential orderwithin a first processing round and the re-processing with theprefetched information each suspended operation in one or moreadditional processing rounds are performed by the computer readableprogram code in a sequential order for each index of the database tablecorresponding with at least one database element such that all indexleaf pages corresponding with a single database element are insertedwith information associated with the single database element prior toproceeding to processing indexes for another database element.
 15. Thecomputer program product of claim 10, wherein the computer readableprogram code is further configured to: subsequent to insertion ofinformation for all index leaf pages corresponding with a set number ofdatabase elements, determine for remaining database elements whether tocontinue prefetching and re-processing with prefetched information forsuspended operations; and in response to a determination to discontinueprefetching and re-processing with prefetched information for suspendedoperations, process indexes in a sequential order for each remainingdatabase element such that each leaf page of an index being processed isobtained from the buffer pool and inserted with informationcorresponding with a remaining database element prior to proceeding to anext index to be processed in the sequential order for the remainingdatabase element.
 16. The computer program product of claim 10, whereinsuspension of an operation in the order due to a resource request by thecomputer readable program code occurs when an index leaf page to beinserted with information corresponding with a database element isabsent from the buffer pool, and prefetching of information with atleast one successive operation being initiated by the computer readableprogram code comprises prefetching of the absent index leaf page intothe buffer pool in parallel with processing the next index in thesequential order.
 17. A system for processing operations to insertdatabase elements into a database object, the system comprising: adatabase including a database object configured to store databaseelements; a processor configured with logic to: process each of aplurality of operations in a sequential order within a first processinground to insert the database elements into the database object, whereinprocessing for at least one operation in the order becomes suspended dueto a resource request, and wherein at least one successive operation isinitiated in response to suspension of one or more prior operations toenable prefetching of information for processing the operations; andre-process with the prefetched information each suspended operation inone or more additional processing rounds until completion of processingfor each of the operations.
 18. The system of claim 17, wherein thedatabase object comprises a database table with a plurality of indexesproviding access to information in the database table, each databaseelement is inserted within a corresponding row within the databasetable, each index comprises a plurality of leaf pages, each leaf pageprovides information associated with a location of at least one databaseelement within the database table, the resource request comprises arequest for an index leaf page during an operation within the sequentialorder, and the insertion of database elements into the database objectincludes insertion of information at index leaf pages corresponding withthe database elements.
 19. The system of claim 17, wherein suspension bythe processor of an operation in the order due to a resource requestoccurs when an index leaf page to be inserted with informationcorresponding with a database element is absent from the buffer pool,and prefetching by the processor of information with at least onesuccessive operation being initiated comprises prefetching of the absentindex leaf page into the buffer pool in parallel with processing thenext index in the sequential order.